﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Configuration;
using System.Data.SQLite;
using System.IO;
using System.Data;
namespace IGIS.Core
{
  public class DBUploadFiles
{
    // Fields
    private SQLiteConnection _conn;
    private string _connectionString = "data source=\"{0}\"";
    private string _spatialFolder = ConfigurationManager.AppSettings["SpatialDataFolder"];
    private string _SQLiteFolder = ConfigurationManager.AppSettings["SQLiteFolder"];

    // Methods
    public DBUploadFiles(string projectName)
    {
        string path =this._SQLiteFolder+projectName + ".db3";
        if (!File.Exists(path))
        {
            throw new SQLiteException(string.Format("Database \"{0}\" not found", projectName));
        }
        this._connectionString = string.Format(this._connectionString, path);
        this._conn = new SQLiteConnection();
        this._conn.ConnectionString = this._connectionString;
        this._conn.Open();
    }

    public bool AddFile(string fileName, string description, string mapName, string featureSource, long uid)
    {
        bool result = false;
        SQLiteTransaction trans = this._conn.BeginTransaction();
        try
        {
            using (SQLiteCommand cmd = this._conn.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO tblFiles(FeatureSourceId, FeatureId, MapName, FileName, Description) VALUES(@FeatureSourceId, @FeatureId, @MapName, @FileName, @Description)";
                SQLiteParameter featSourceIdParam = new SQLiteParameter("@FeatureSourceId", this.GetFeatureSourceId(featureSource, true));
                SQLiteParameter featIdParam = new SQLiteParameter("@FeatureId", uid);
                SQLiteParameter mapNameParam = new SQLiteParameter("@MapName", mapName);
                SQLiteParameter fileNameParam = new SQLiteParameter("@FileName", fileName);
                SQLiteParameter descriptionParam = new SQLiteParameter("@Description", description);
                cmd.Parameters.AddRange(new SQLiteParameter[] { featSourceIdParam, featIdParam, mapNameParam, fileNameParam, descriptionParam });
                cmd.ExecuteNonQuery();
            }
            trans.Commit();
            result = true;
        }
        catch (SQLiteException)
        {
            trans.Rollback();
        }
        finally
        {
            if (trans != null)
            {
                trans.Dispose();
            }
        }
        return result;
    }

    public string DeleteFile(long id)
    {
        SQLiteCommand cmd;
        SQLiteParameter idParam;
        string result = string.Empty;
        using (cmd = this._conn.CreateCommand())
        {
            cmd.CommandText = "SELECT FileName FROM tblFiles WHERE Id=@Id";
            idParam = new SQLiteParameter("@Id", id);
            cmd.Parameters.Add(idParam);
            SQLiteDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                result = (string) reader["FileName"];
            }
            reader.Close();
        }
        using (cmd = this._conn.CreateCommand())
        {
            cmd.CommandText = "DELETE FROM tblFiles WHERE Id=@Id";
            idParam = new SQLiteParameter("@Id", id);
            cmd.Parameters.Add(idParam);
            cmd.ExecuteNonQuery();
        }
        return result;
    }

    ~DBUploadFiles()
    {
        if (this._conn != null)
        {
            this._conn.Dispose();
        }
    }

    public long GetFeatureSourceId(string featureSource, bool insertIfNotExists)
    {
        SQLiteCommand cmd;
        SQLiteParameter featureSourceParam;
        long result = 0L;
        using (cmd = this._conn.CreateCommand())
        {
            cmd.CommandText = "SELECT Id FROM lkpFeatureSource WHERE FeatureSource = @FeatureSource";
            featureSourceParam = new SQLiteParameter("@FeatureSource", featureSource);
            cmd.Parameters.Add(featureSourceParam);
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    result = (long) reader["Id"];
                }
            }
        }
        if ((result != 0L) || !insertIfNotExists)
        {
            return result;
        }
        using (cmd = this._conn.CreateCommand())
        {
            cmd.CommandText = "INSERT INTO lkpFeatureSource(FeatureSource) VALUES(@FeatureSource)";
            featureSourceParam = new SQLiteParameter("@FeatureSource", featureSource);
            cmd.Parameters.Add(featureSourceParam);
            cmd.ExecuteNonQuery();
            return this.GetFeatureSourceId(featureSource, insertIfNotExists);
        }
    }

    public IDataReader GetFiles(string featureSource, long uid)
    {
        using (SQLiteCommand cmd = this._conn.CreateCommand())
        {
            cmd.CommandText = "SELECT Id, MapName, FileName, Description, UploadDate FROM tblFiles WHERE FeatureSourceId = @FeatureSourceId AND FeatureId = @FeatureId";
            SQLiteParameter featSourceIdParam = new SQLiteParameter("@FeatureSourceId", this.GetFeatureSourceId(featureSource, false));
            SQLiteParameter featIdParam = new SQLiteParameter("@FeatureId", uid);
            cmd.Parameters.AddRange(new SQLiteParameter[] { featSourceIdParam, featIdParam });
            return cmd.ExecuteReader();
        }
    }

    public long GetLastInsertedId()
    {
        using (SQLiteCommand cmd = this._conn.CreateCommand())
        {
            cmd.CommandText = "SELECT last_insert_rowid()";
            return (long) cmd.ExecuteScalar();
        }
    }

    public bool IsExists(string fileName, string featureSource, long uid)
    {
        bool result = false;
        using (SQLiteCommand cmd = this._conn.CreateCommand())
        {
            cmd.CommandText = "SELECT Id FROM tblFiles WHERE FeatureSourceId = @FeatureSourceId AND FeatureId = @FeatureId AND FileName=@FileName";
            SQLiteParameter featSourceIdParam = new SQLiteParameter("@FeatureSourceId", this.GetFeatureSourceId(featureSource, true));
            SQLiteParameter featIdParam = new SQLiteParameter("@FeatureId", uid);
            SQLiteParameter fileNameParam = new SQLiteParameter("@FileName", fileName);
            cmd.Parameters.AddRange(new SQLiteParameter[] { featSourceIdParam, featIdParam, fileNameParam });
            SQLiteDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                result = true;
            }
        }
        return result;
    }

    public bool UpdateFile(long id, string description)
    {
        using (SQLiteCommand cmd = this._conn.CreateCommand())
        {
            cmd.CommandText = "UPDATE tblFiles SET Description=@Description WHERE Id=@Id";
            SQLiteParameter idParam = new SQLiteParameter("@Id", id);
            SQLiteParameter descrParam = new SQLiteParameter("@Description", description);
            cmd.Parameters.Add(idParam);
            cmd.Parameters.Add(descrParam);
            return (cmd.ExecuteNonQuery() > 0);
        }
    }
}


}